package SqlConnect;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

public class SqlInvestment {
    public static final String[] guiTag = { "项目名", "所投单位", "投资开始日期", "预期结束日期", "投资金额(￥)", "年收益(%)" };
    public static final String[] sqlTag = { "Name", "Company", "BeginDate", "EndDate", "Amount", "YearlyEarnings" };
    private final Statement st;

    public SqlInvestment(Statement st) throws SQLException {
        this.st = st;
        st.execute(
                "CREATE TABLE IF NOT EXISTS `data` (`Name` VARCHAR(100),`Company` TEXT NOT NULL,`BeginDate` DATE NOT NULL,`EndDate` DATE NOT NULL,`Amount` DECIMAL(15,2) NOT NULL,`YearlyEarnings` DECIMAL(15,2) NOT NULL,PRIMARY KEY (`Name`))ENGINE=InnoDB DEFAULT CHARSET=utf8;");
    }

    public boolean add(String[] args) {
        StringBuilder in = new StringBuilder(
                "INSERT INTO data(`Name`,`Company`,`BeginDate`,`EndDate`,`Amount`,`YearlyEarnings`) VALUES(");
        for (int i = 0; i < 6; i++) {
            args[i] = args[i].strip();
            if (args[i].equals(""))
                return false;
            in.append("'").append(args[i]).append("'");
            if (i != 5)
                in.append(",");
        }
        try {
            st.execute(in + ");");
        } catch (SQLException se) {
            return false;
        }
        return true;
    }

    private Vector<Vector<String>> query(String s) {
        try {
            Vector<Vector<String>> ret = new Vector<>();
            ResultSet e = st.executeQuery(s);
            while (e.next()) {
                Vector<String> tmp = new Vector<>();
                for (int j = 0; j < 6; j++) {
                    tmp.add(e.getString(j + 1));
                }
                ret.add(tmp);
            }
            return ret;
        } catch (SQLException e) {
            return null;
        }
    }

    public Vector<Vector<String>> queryName(String arg) {
        return query("SELECT * FROM data WHERE Name='" + arg + "';");
    }

    public Vector<Vector<String>> queryYear(String arg) {
        return query("SELECT * FROM data WHERE YEAR(BeginDate)='" + arg + "';");
    }

    public Vector<Vector<String>> queryBeforeNow() {
        return query("SELECT * FROM data WHERE EndDate<DATE(NOW());");
    }

    public Vector<Vector<String>> queryAfterNow() {
        return query("SELECT * FROM data WHERE EndDate>=DATE(NOW());");
    }

    public Vector<Vector<String>> queryEarning() {
        return query(
                "SELECT Name,Company,BeginDate,EndDate,Amount,FORMAT(YearlyEarnings * (YEAR(EndDate)-YEAR(BeginDate))/100*Amount,2) FROM data WHERE EndDate<DATE(NOW());");
    }
}